#!/usr/bin/php -q
<?php
  // Database credentials
  require_once('settings.php');
  
  // SQL queries
  require_once('queries.php');
  
  $mysql_client = "/usr/local/zend/mysql/bin/mysql.client";
  $mysql_dump = "/usr/local/zend/mysql/bin/mysqldump";
  if (isset($argv[1])) {
    $iso_a2 = $argv[1];
  } else {
    exit('No country code provided.');
  }
  $file_store_path = '/tmp/';
  $file_extension_tab = '.tab';
  $file_extension_xls = '.xls';
  $tab_delimited_file_name = $file_store_path . 'checklist_from_occu_' . $iso_a2 . $file_extension_tab;
  $xls_file_name = $file_store_path. 'checklist_from_occu_' . $iso_a2 . $file_extension_xls;
  $tab_delimited_file = "'". $tab_delimited_file_name . "'";

  function execute_query($conn, $sql, $action) {
    $query = $conn->exec($sql);
    $row_string = ($query > 1) ? 'rows' : 'row';
    fwrite(STDOUT, "$action... $query $row_string affected.\n");
  }

  // Get IDs identified to the species level
  select_database('portal');
  $DBH = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
  $DBH->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql';
 
  fwrite(STDOUT, "Getting nub IDs that are associated with occurrence data from $iso_a2...");
  $get_all_nub_occu = $DBH->prepare($get_all_nub_sql);
  $get_all_nub_occu_result = $get_all_nub_occu->execute(array(":iso_a2" => $iso_a2));
  $result = ($get_all_nub_occu_result == TRUE) ? 'done' : 'failed';
  fwrite(STDOUT, "$result\n");
  $result = '';
  
  execute_query($DBH, $get_all_nub_sql_index, 'Create Index');
  execute_query($DBH, $get_all_nub_sql_sp, 'Make sure species id of sub specific entries are there');
  execute_query($DBH, $get_all_nub_sql_distinct, 'Retrieve only unique entries');
  execute_query($DBH, $get_all_nub_sql_dis_index, 'Create Index');
  execute_query($DBH, $get_all_nub_sql_drop, 'Drop the table no longer needed');

  // Collect names in parent-child format
  fwrite(STDOUT, "\nConstructing parent-child entries from subspecies...\n");
  execute_query($DBH, $collect_names_sql, 'Inserting subspecific entries');
  execute_query($DBH, $collect_names_sql_s, 'Inserting specific entries');
  execute_query($DBH, $collect_names_sql_g, 'Inserting generic entries');
  execute_query($DBH, $collect_names_sql_f, 'Inserting family level entries');
  execute_query($DBH, $collect_names_sql_o, 'Inserting order level entries');
  execute_query($DBH, $collect_names_sql_c, 'Inserting class level entries');
  execute_query($DBH, $collect_names_sql_p, 'Inserting phylum level entries');
  execute_query($DBH, $collect_names_sql_k, 'Inserting kingdom level entries');
  execute_query($DBH, $collect_names_sql_delete, 'Deleting null entries');
  
  $DBH = null;

  // Run check-checklist.php to fill null parentID entries
  system('./check-checklist.php');

  // Capitalise rank name
  select_database('portal');
  $DBH = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
  $DBH->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql';
  execute_query($DBH, $capitalise_rank_sql, 'Capitalise rank');
  $DBH = null;

  // Export the parent_child_bko table and import locally for processing
  fwrite(STDOUT, "\nGenerating parent-child table dump...\n");
  $export_cmd = $mysql_dump . $portal_mysql_cred . "parent_child_bko > /tmp/parent_child_bko.dump";
  system($export_cmd);

  select_database('local');
  $DBH = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
  $DBH->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql';
  
  execute_query($DBH, 'CREATE DATABASE name_processing;', 'Create a local database for processing');

  fwrite(STDOUT, "Importing parent-child table dump...\n");
  $import_cmd = $mysql_client . " name_processing < /tmp/parent_child_bko.dump";
  system($import_cmd);

  fwrite(STDOUT, "Deleting unused dump file...");
  $delete = unlink('/tmp/parent_child_bko.dump');
  $delete_result = ($delete == TRUE) ? 'File deleted.' : 'File not deleted.';
  fwrite(STDOUT, "$delete_result\n");

  fwrite(STDOUT, "\nGenerating parent-child Tab-delimted file...\n");
  // Check if a file with a same name already exists
  $file_exists = file_exists($tab_delimited_file_name);
  if ($file_exists === TRUE) {
    
    $delete_cmd = str_replace(array(":pass", ":file"), array($shell_pass, $tab_delimited_file_name), $delete_cmd);
    $deleted = system($delete_cmd);

    if ($deleted == FALSE) {
      fwrite(STDOUT, "Old tab-delimited file exists but deletion failed.\n");
    } else {
      fwrite(STDOUT, "Old tab-delimited file exists and have been deleted.\n");
    }
  }
  $export_csv_cmd = str_replace(":file", $tab_delimited_file, $export_csv_cmd);
  system($export_csv_cmd);
  $chmod_cmd = str_replace(array(":pass", ":file"), array($shell_pass, $tab_delimited_file_name), $chmod_cmd);
  system($chmod_cmd);
  fwrite(STDOUT, "\nFile saved as $tab_delimited_file_name\n");
  
  // Convert Tab-delimited file to XLS format.
  require('PHPExcel/IOFactory.php');
  require('PHPExcel.php');
  
  fwrite(STDOUT, "Converting to XLS format...\n");
  ini_set('memory_limit', '-1');
  ini_set('date.timezone', 'UTC');
  $objReader = PHPExcel_IOFactory::createReader('CSV');
  $objReader->setDelimiter("\t");
  $objPHPExcel = $objReader->load($tab_delimited_file_name);
  $sheet = $objPHPExcel->getActiveSheet();
  $sheet->insertNewRowBefore(1, 1);
  $sheet->setCellValue("A1", "Term name")
    ->setCellValue("B1", "Parent term name")
    ->setCellValue("C1", "Term description")
    ->setCellValue("D1", "GUID")
    ->setCellValue("E1", "Parent GUID")
    ->setCellValue("F1", "Associated accepted name")
    ->setCellValue("G1", "Authors")
    ->setCellValue("H1", "Page number")
    ->setCellValue("I1", "Rank")
    ->setCellValue("J1", "Reference")
    ->setCellValue("K1", "Unacceptability reason")
    ->setCellValue("L1", "Unit Indicator 1")
    ->setCellValue("M1", "Unit Indicator 2")
    ->setCellValue("N1", "Unit Indicator 3")
    ->setCellValue("O1", "Unit Indicator 4")
    ->setCellValue("P1", "Unit name 1")
    ->setCellValue("Q1", "Unit name 2")
    ->setCellValue("R1", "Unit name 3")
    ->setCellValue("S1", "Unit name 4")
    ->setCellValue("T1", "Usage")
    ->setCellValue("U1", "Vernacular names");
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  $objWriter->save($xls_file_name);
  
  $xls_file_exists = file_exists($xls_file_name);
  if ($xls_file_exists == TRUE) {
    fwrite(STDOUT, "XLS file saved as $xls_file_name\n");
  } 
  
  fwrite(STDOUT, "\nCleaning up temporary databases, tables and files...\n");
  execute_query($DBH, 'DROP DATABASE name_processing;', 'Delete unused database');
  $DBH = null;
  fwrite(STDOUT, "\n");

  // Drop databases created on portal
  select_database('portal');
  $DBH = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
  $DBH->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql';
  
  fwrite(STDOUT, "Cleaning up temporary tables on portal...\n");
  execute_query($DBH, $clean_db_sql_t, 'Dropping taxon_id_from_occurrence_distinct');
  execute_query($DBH, $clean_db_sql_pc, 'Dropping parent_child_bko');

  $DBH = null;

  fwrite(STDOUT, "\nChecklist from country occurrence data generation finished.\n");

?>